package com.techgene.kanban.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Component;

import com.techgene.kanban.dao.ProjectDao;
import com.techgene.kanban.domain.Project;
import com.techgene.kanban.domain.User;
import com.techgene.kanban.exception.KBDaoException;
import com.techgene.kanban.util.AppConstants;

@Component
public class ProjectDaoImpl implements ProjectDao {

	@Autowired
	DataSource dataSource;
	private Logger logger = LoggerFactory.getLogger(ProjectDaoImpl.class);

	
	
	/**
	 * retrieving users from database that match with searchText
	 * @param selectedUsernames List
	 * @param searchText String
	 * @throws KBDaoException
	 * @return
	 */
	public List retrieveUsers(String searchText, final List selectedUsernames)
			throws KBDaoException {

		logger.debug(" >> retrieveUsers , [ searchText = {} ] ", searchText);

		List users = new ArrayList();

		try {

			JdbcTemplate template = new JdbcTemplate(dataSource);
			String sql = "select username, password, role, email, firstname, lastname from tbl_user where username like '"+ searchText + "%' and role !='admin'";
			JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			users = jdbcTemplate.query(sql, new ResultSetExtractor<List>() {
				public List extractData(ResultSet rs) throws SQLException,
						DataAccessException {
					List usersList = new ArrayList();
					while (rs.next()) {
						User user = new User();
						if (!selectedUsernames.contains(rs.getString("username"))) {
							logger.info(rs.getString("username"));
							user.setUsername(rs.getString("username"));
							user.setPassword(rs.getString("password"));
							user.setRole(rs.getString("role"));
							user.setEmail(rs.getString("email"));
							user.setFirstName(rs.getString("firstname"));
							user.setLastName(rs.getString("lastname"));
							usersList.add(user);
						}
						logger.debug("userslist = {} ", usersList);

					}

					return usersList;
				}
			});
		} catch (Exception e) {

			/* for any other errors */
			throw new KBDaoException(
					AppConstants.ERROR.DAO_UNKNOWN_ERROR_ADD_PROJECT_DETAILS.code,
					AppConstants.ERROR.DAO_UNKNOWN_ERROR_ADD_PROJECT_DETAILS.msg,
					e);

		} catch (Throwable e) {

			throw new KBDaoException(
					AppConstants.ERROR.DAO_UNKNOWN_ERROR_ADD_PROJECT_DETAILS.code,
					AppConstants.ERROR.DAO_UNKNOWN_ERROR_ADD_PROJECT_DETAILS.msg,
					e);
		}

		logger.debug(" << retrieveUsers , [ users = {} ]", users);
		return users;
	}

	
	
	
	/**
	 * Adding project to database
	 * @param projectName String
	 * @param projectDescription String
	 * @param selectedUsers List
	 * @param int projectId
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public int addOrUpdateProject(String projectName, String projectDescription,List selectedUsers,String type,int projectId) throws KBDaoException 
	{

		logger.debug(" >> addProject [projectName = {}, ProjectDescription = {} , users= {} ,projectId = {}] ",projectName, projectDescription, selectedUsers,projectId);
		
		//if type is update performing update operations else performing insert operation
		if(type.equalsIgnoreCase("update"))
		{
			int update=0;
		
			try {
				JdbcTemplate template = new JdbcTemplate(dataSource);

				int res = template.update("update tbl_project set project_description=?, project_name=? where project_id=?",projectDescription, projectName,projectId);
				Integer project_id = template.queryForObject("select project_id from tbl_project where project_name=?",Integer.class, projectName);
				logger.debug("projet_id = {} ", project_id);
			
				/*
				 * deleting existing users
				 */
				template.update("delete from tbl_project_user where project_id=?",project_id);

				/*
				 * assigning users to project
				 */
				for (int k = 0; k < selectedUsers.size(); k++) 
				{
					Integer user_id = template.queryForObject("select user_id from tbl_user where username=?",Integer.class, selectedUsers.get(k));
					String tbu = "insert into tbl_project_user values(?,?)";
					update = template.update(tbu, project_id, user_id);
				}
				
		}catch (Exception e) {
			
			throw new KBDaoException(AppConstants.ERROR.DAO_UNKNOWN_ERROR_UPDATE_PROJECT.code,AppConstants.ERROR.DAO_UNKNOWN_ERROR_UPDATE_PROJECT.msg, e);
			
		}catch (Throwable e) {
			
			throw new KBDaoException(AppConstants.ERROR.DAO_UNKNOWN_ERROR_UPDATE_PROJECT.code,AppConstants.ERROR.DAO_UNKNOWN_ERROR_UPDATE_PROJECT.msg, e);
		}

		logger.debug(" << updateProjectDetails [ update ={} ] " , update);
		return update;
	}else
	{
		
		int storeCount = 0, result = 0;
		try {
			JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

			storeCount = jdbcTemplate.update(AppConstants.INSERT_PROJECT_DETAILSLIST_QUERY, projectName,projectDescription);

			 //if project details are inserted successfully 
			if (storeCount == 1) {
				logger.debug("project details are inserted successfully.");
				projectId=jdbcTemplate.queryForInt(AppConstants.SELECT_PROJECT_ID_QUERY,projectName);
				logger.debug(" projectId ={} ", projectId);

				 //select project and assign to the user 
				for (int k = 0; k < selectedUsers.size(); k++) {

					Integer userId = jdbcTemplate.queryForObject(AppConstants.SELECT_USER_ID_QUERY, Integer.class,selectedUsers.get(k));
					logger.debug("projectId ={}, userName ={}, userId={} ",projectId, selectedUsers.get(k), userId);
					result = jdbcTemplate.update(AppConstants.INSERT_PROJECT_ID_TO_USER_ID_QUERY,projectId, userId);
					logger.debug(" project assign to the user ={} , result ={} ",userId, result);

				}
			}

		} catch (Exception e) {

			throw new KBDaoException(AppConstants.ERROR.DAO_UNKNOWN_ERROR_ADD_PROJECT_DETAILS.code,AppConstants.ERROR.DAO_UNKNOWN_ERROR_ADD_PROJECT_DETAILS.msg,e);
		} catch (Throwable e) {

			throw new KBDaoException(AppConstants.ERROR.DAO_UNKNOWN_ERROR_ADD_PROJECT_DETAILS.code,AppConstants.ERROR.DAO_UNKNOWN_ERROR_ADD_PROJECT_DETAILS.msg,e);
		}

		logger.debug("<< addProjectDetails status = {} ", result);
		return result;
	}
  }

	
	
	
	/**
	 * retrievig projects from database
	 * @return
	 */
	public List<Project> listProjects() throws KBDaoException {

		logger.debug(">> list project details..");

		List<Project> projectList = new ArrayList<Project>();
		try {

			String sql = "select * from tbl_project";
			JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			projectList = jdbcTemplate.query(sql,
					new ResultSetExtractor<List<Project>>() {
						public List<Project> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<Project> projects = new ArrayList<Project>();

							while (rs.next()) {
								Project project = new Project();
								project.setProjectName(rs.getString("project_name"));
								project.setProjectDescription(rs.getString("project_description"));
								projects.add(project);
								logger.debug("projectname:{},projectdescription:{}",project.getProjectName(),project.getProjectDescription());
							}
							return projects;
						}
					});

		} catch (Exception e) {

			throw new KBDaoException(AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.code,AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.msg, e);

		} catch (Throwable e) {

			throw new KBDaoException(AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.code,AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.msg, e);
		}

		logger.debug("<< projects list [projectLists  = {} ,projects = {} ]",projectList);

		return projectList;

	}
	
	
	
	
	/**
	 * deleting project from database
	 * @param projectName String
	 * @return
	 */
	public int deleteProject(String projectName) throws KBDaoException{
		
		logger.debug(" >> deleteProject [ projectName = {} ]" , projectName);
		int result = 0;
		
		try{
			
			JdbcTemplate template = new JdbcTemplate(dataSource);
			String sql="delete from tbl_project where project_name=?";
			result=template.update(sql,projectName);	
			
		}catch (Exception e) {

			throw new KBDaoException(AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.code,AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.msg, e);

		} catch (Throwable e) {

			throw new KBDaoException(AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.code,AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.msg, e);
		}
		return result;
		
		
	}
	
	
	
	
	/**
	 * retrieving users for the specified project
	 * @param projectName String
	 * @return
	 */
	public Map retrieveUsersOfProject(String projectName) throws KBDaoException{
		
		logger.debug(" >> retrieveUsersOfProject [ projectName = {} ] " , projectName);
		List users=new ArrayList();
		Project project=new Project();
		int project_id=0;
		Map projectDetails=new HashMap();

		try{
			
			JdbcTemplate template = new JdbcTemplate(dataSource);
			String query="select project_id,project_description from tbl_project where project_name=?";
			List result=template.queryForList(query,projectName);
			logger.debug("result= {}",result);
			Map projectMap=(HashMap)result.get(0);
			project_id=(Integer)projectMap.get("project_id");
			String project_description=(String)projectMap.get("project_description");
			project.setProjectDescription(project_description);
			project.setProjectName(projectName);
			logger.debug(" project = {} ",project);
			query="select user_id from tbl_project_user where project_id=?";
			result=template.queryForList(query, project_id);
			logger.debug("result={}",result);
			List user_ids=new ArrayList();
			
			for(int i=0;i<result.size();i++){
				
				projectMap=(HashMap)result.get(i);
				user_ids.add(projectMap.get("user_id"));

			}
			final List usersList = new ArrayList();

			logger.debug("projectMap = {} ",projectMap);
			logger.debug("user_ids = {} ",user_ids);
			for(int i=0;i<user_ids.size();i++){
				
				query="select * from tbl_user where user_id="+user_ids.get(i);
				users = template.query(query,new ResultSetExtractor<List>() {
					public List extractData(ResultSet rs) throws SQLException,
							DataAccessException {
						while (rs.next()) {
							User user = new User();
								logger.info(rs.getString("username"));
								user.setUsername(rs.getString("username"));
								user.setRole(rs.getString("role"));
								user.setFirstName(rs.getString("firstname"));
								user.setLastName(rs.getString("lastname"));
								usersList.add(user);
							logger.debug("userslist = {} ", usersList);

						}

						return usersList;
					}
				});
				logger.debug("users = {} ",users);
			}
			
			projectDetails.put("projectName", projectName);
			projectDetails.put("projectDescription", project_description);
			projectDetails.put("users", users);
			projectDetails.put("projectId", project_id);
		}catch (Exception e) {

			throw new KBDaoException(AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.code,AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.msg, e);

		}catch (Throwable e) {

			throw new KBDaoException(AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.code,AppConstants.ERROR.DAO_UNKNOWN_ERROR_LIST_PROJECTS.msg, e);
		}
		
		logger.debug(" << retrieveUsersOfproject [projectDetails= {} ]",projectDetails);
		return projectDetails;
	}

}
